import time
import gevent
import os
from threading import Thread
from multiprocessing import Process,Queue
from concurrent.futures import ThreadPoolExecutor
from utils.mysqlUtils.db_utils import DB
from utils.mysqlUtils.sql_server_utils import MSSQL
from utils.noticeUtils.dingtalkControl import DingTalkSendMsg
from utils.readFileUtils.excel_control import write_excle_data
# from public_way import public_invoking

class Testishotdata:

    """
    Listing 迁移
    """

    # 初始化同步数据验证
    def base_comparison(self, erp_sql, cold_sql):

        # 获取erp库数据
        erp_data = self.erp_db(erp_sql)
        cold_data = self.cold_db(cold_sql)

        sum = len(erp_data)

        # 创建Executor对象，指定线程池中线程数
        executor = ThreadPoolExecutor(10)
        # 调用线程池中线程去执行函数
        future = executor.submit(self.data_comparison, erp_data, cold_data)
        error_data = future.result()

        self.dingtalk_report(sum, error_data)


    # 初始化冷热数据表数据验证
    def cold_hot_comparison(self,filedir,hot_sql):

        # 创建队列
        q = Queue(maxsize=1000000)
        # 接收进程ID队列
        q1 = Queue()
        # 读取热数据sql
        content = self.read_file(filedir).split('\n')
        # 开启2个进程处理
        pro_list = []
        for i in range(1):
            pname = 'pro-{}'.format(i)
            print('创建进程{}'.format(pname))
            p = Process(target=self.process_work, args=(q, q1, content))
            p.start()
            pro_list.append(p)
            # 让主进程等待子进程执行结束在往下执行
        time.sleep(5)

        pid_son = q1.get(timeout=0.01)
        # os.kill(pid_son,9)
        print("杀死子进程")
        for p in pro_list:
            p.join()

        # 获取erp数据
        hot_data = []
        while not q.empty():
            hot_data.append(q.get())
        sum_data = len(hot_data)

        # 获取热数据表数据
        hot_base = self.cold_db(hot_sql)

        # 创建Executor对象，指定线程池中线程数
        executor = ThreadPoolExecutor(10)
        # 调用线程池中线程去执行函数
        future = executor.submit(self.data_comparison, hot_data, hot_base)
        error_data = future.result()

        # self.dingtalk_report(sum_data, error_data)
        print("验证完成",len(error_data))

    # 初始化冷热数据表数据验证
    def hot_cold_comparison(self, file, hot_sql, filename):

        # 读取热数据sql
        content = self.read_file(file).split('\n')

        # 获取erp数据(去重)
        data_list = []
        for i in content:
            data = self.erp_db(i)
            for j in data:
                if j not in data_list:
                    data_list.append(j)
        sum_data = len(data_list)

        # 获取热数据表数据
        hot_base = self.cold_db(hot_sql)

        # 创建Executor对象，指定线程池中线程数
        executor = ThreadPoolExecutor(10)
        # 调用线程池中线程去执行函数
        future = executor.submit(self.data_comparison, data_list, hot_base)
        error_data = future.result()

        # 将检测到的异常的 敏感词 & 品牌商标词 数据写入CSV文档
        # write_excle_data(error_data, filename)
        self.dingtalk_report(sum_data, error_data)
        return error_data

    # ERP生产库
    def erp_db(self, sql):

        erp_base = MSSQL("sqlserver_db_erp_base")
        erp_res = erp_base.ExecQuery(sql, value=())
        return erp_res

    # 冷数据库
    def cold_db(self, sql):

        cold_base = DB("mysql_db_code_base")
        cold_res = cold_base.execute_sql_many(sql)
        return cold_res

    # 执行多条sql获取结果推到队列
    def test_sql_data(self, q, sql_list):
        print("开始读取数据")
        for i in sql_list:
            data = self.erp_db(i)
            print(f"成功获取sql_list.index{i}的元素")
            for j in data:
                q.put(j)
            print("获取的数据已推进队列")

    # 数据比对
    def data_comparison(self, erp_data, cold_data):

        error_list = []
        for i in cold_data:
            if i not in erp_data:
                error_list.append(i)
        return error_list

    # 读取文件
    def read_file(self, filedir):
        with open(filedir, encoding='utf-8') as file:
            content = file.read()
        return content

    # dingtalk机器人预警
    def dingtalk_report(self, sum_data, error_data):

        if len(error_data) > 0:
            dingtalk_report = f'''检测数据合计{sum_data}条 \n
                                存在异常数据{len(error_data)}条 \n
                                异常数据明细: \n  {error_data} \n
                                '''
            DingTalkSendMsg().send_migration_report(dingtalk_report)
        else:
            dingtalk_report = f'''检测数据合计{sum_data}条 \n
                                存在异常数据{len(error_data)}条 \n
                                '''

        DingTalkSendMsg().send_migration_report(dingtalk_report)

    # 多线程
    def process_work(self, q, q1, sql_list):
        print('主进程的pid为: ', os.getpid(), '当前子进程的父进程的pid为: ', os.getppid())
        q1.put(os.getpid())
        # 创建3个线程，并执行
        thread_list = []
        for i in range(3):
            t = Thread(target=self.test_sql_data, args=(q, sql_list))
            print(f"创建线程{i}")
            thread_list.append(t)
            t.start()
        # 让主线程堵塞，等待子线程执行完后在执行
        for t in thread_list:
            t.join()

if __name__ == '__main__':
    # 全量同步
    # sql = "SELECT OrderSourceSKU, ASINorItemID, SKU FROM RB_Amazon_Product_Base(nolock) WHERE CustomerID = 1 AND OrderSourceID in (5059,5005,5086)  AND OrderSourceSKUID > 0 AND AddTime < '2022-11-14';"
    # mysql_sql = "SELECT OrderSourceSKU, ASINorItemID, SKU FROM aml_amazon_product_base_dep86 WHERE OrderSourceID in (5059,5005,5086);"
    # Testishotdata().base_comparison(erp_sql=sql,cold_sql=mysql_sql)

    filedir = "D:\web_autotest\zhcx-service-test\Common\热数据sql.csv"
    sql = "SELECT OrderSourceSKU, ASINorItemID, SKU FROM aml_amazon_product_base_dep86 WHERE OrderSourceID = 5086 AND IsHotData = 1;"
    file = "D:\web_autotest\zhcx-service-test\Common\\"
    res = Testishotdata().hot_cold_comparison(filedir,sql,file)
    print(res)
